﻿using MesBSW.DAL.MesDB;
using MesBSW.DAL.RoleDB;
using MesBSW.Tools;
using MesBSW.View;
using SqlSugar;
using System;
using System.Collections.Generic;
namespace MesBSW.BLL.Mes
{
    /// <summary>
    /// 异常工单界面业务逻辑
    /// </summary>
    public class AbnormalOrderBLL : BaseControler
    {
        List<TargetProdProcess> targetProdProcess = new List<TargetProdProcess>();
        List<ProdProcScan> scanInfo = new List<ProdProcScan>();
        List<StatusInfo> StatusInfo = new List<StatusInfo>();
        List<BaseUserModel> User = new List<BaseUserModel>();
        private SqlSugarClient Db = SqlSugares.GetInstance();
        BaseControler bs = new BaseControler();
        /// <summary>
        /// 查询异常工单数据
        /// </summary>
        /// <returns></returns>
        public List<AbnormalOrderResult> SelectAbnormalOrder(string prod_number, string PlayDate, string EndDate)
        {

            List<int> deptIds = bs.SelectUserDept();
            string deptStr = "";
            for (int i = 0; i < deptIds.Count; i++)
            {
                deptStr += "'" + deptIds[i] + "'" + ",";
            }
            deptStr = deptStr.Substring(0, deptStr.Length - 1);
            return Db.SqlQueryable<AbnormalOrderResult>($@"SELECT TOP 200 
                T1.prod_number,
                T2.status_value,
                T3.status_value AS status_value1,
                T4.site_name,
                T5.scan_date,
                T6.real_name,
				T1.curr_site_id,
				T1.prod_qty
            FROM
                target_prod_process T1
                LEFT JOIN status_info T2 ON T1.prod_status = T2.id
                LEFT JOIN status_info T3 ON T1.err_status = T3.id
                LEFT JOIN site_info T4 ON T1.curr_site_id = T4.id
                LEFT JOIN (
                    SELECT site_id, prod_number, scan_date, T2.user_name, T1.scan_user,
                        ROW_NUMBER() OVER (PARTITION BY site_id, prod_number ORDER BY scan_date DESC) AS rn
                    FROM pp_scan T1
                    LEFT JOIN base_user T2 ON T1.scan_user = T2.user_name
                    WHERE T1.scan_date > '{PlayDate}' AND T1.scan_date < '{EndDate}'
                ) T5 ON T1.curr_site_id = T5.site_id AND T5.prod_number = T1.prod_number AND T5.rn = 1
                LEFT JOIN base_user T6 ON T5.scan_user = T6.user_name
            WHERE
                T1.prod_number LIKE '%{prod_number}%' and T1.dept_id in ({deptStr})
            GROUP BY
                T1.prod_number,
                T2.status_value,
                T3.status_value,
                T4.site_name,
                T5.scan_date,
                T6.real_name,
				T1.curr_site_id,
				T1.prod_qty
            ORDER BY
                CASE
                    WHEN T3.status_value IN ('严重异常', '普通异常') THEN 1
                    ELSE 2
                END,
                T5.scan_date DESC").ToList();
        }
        /// <summary>
        /// 临时存储实体类
        /// </summary>
        public class AbnormalOrderResult
        {
            public string prod_number { get; set; }
            public DateTime scan_date { get; set; }
            public string site_name { get; set; }
            public string status_value { get; set; }
            public string status_value1 { get; set; }
            public string real_name { get; set; }
            public int curr_site_id { get; set; }
            public decimal prod_qty { get; set; }
        }
        /// <summary>
        /// 修改异常状态为正常
        /// </summary>
        public void UpdateAbnormalOrder(string ProdNumber, int id)
        {
            //修改明细表状态
            Db.Ado.ExecuteCommand($@"update abnormal_detail set end_time = '{DateTime.Now}',update_user = '{MainFrm.LoginUser.user_name}',update_date = '{DateTime.Now}' where id = {id}");

            //修改工单表状态
            var number = Db.SqlQueryable<object>($@"select
	            *
            from
	            abnormal_detail
            where
	            end_time is null and prod_number = '{ProdNumber}'").ToList();
            //没有异常明细的时候
            if (number.Count == 0)
            {
                //修改工单表状态
                Db.Ado.ExecuteCommand($@"update target_prod_process set err_status = 19 where prod_number = '{ProdNumber}'");
            }
        }
        /// <summary>
        /// 查询异常明细
        /// </summary>
        /// <returns></returns>
        public List<ProdAbnormal_DetailTemp> SelectOrderAbnormal_Detail(string prod_number = "", string PlayDate = "", string EndDate = "")
        {
            List<int> deptIds = bs.SelectUserDept();
            string deptStr = "";
            for (int i = 0; i < deptIds.Count; i++)
            {
                deptStr += "'" + deptIds[i] + "'" + ",";
            }
            if(deptIds.Count!=0)
            {
                deptStr = deptStr.Substring(0, deptStr.Length - 1);
            }
            else
            {
                deptStr = "0";
            }
            string sql = $@"select top 9999
                ROW_NUMBER() OVER (ORDER BY 
				CASE
					WHEN end_time IS NULL THEN 0
					ELSE 1
				END,
				end_time,
				CASE
					WHEN status_name = '严重异常' THEN 0
					ELSE 1
				END) as seq,
	            T1.id,
	            t1.prod_number prod_number1,
	            site_name,
	            status_name,
	            abnormal_qty,
	            reason_name,
	            T1.remark,
	            begin_time,
	            end_time,
	            T2.real_name,
                CAST(DATEDIFF(minute, begin_time, end_time) / 1440 as VARCHAR) + ' 天 ' +
                CAST((DATEDIFF(minute, begin_time, end_time) % 1440) / 60 as VARCHAR) + ' 小时 ' +
                CAST((DATEDIFF(minute, begin_time, end_time) % 1440) % 60 as VARCHAR) + ' 分' as 'total_time'
            from
	            abnormal_detail T1 left join base_user T2 on T1.create_user = T2.user_name
                left join target_prod_process T3 on T1.prod_number = T3.prod_number
            where
                t1.prod_number like '%{prod_number}%' and convert(char(10),begin_time,120) >= '{PlayDate}' and convert(char(10),begin_time,120) <= '{EndDate}' and T3.dept_id in ({deptStr})
            ORDER BY
                CASE
                    WHEN end_time IS NULL THEN 0
                    ELSE 1
                END,
                end_time,
				CASE
				    WHEN status_name = '严重异常' THEN 0
				    ELSE 1
				END;";
            return Db.SqlQueryable<ProdAbnormal_DetailTemp>(sql).ToList();
        }
        /// <summary>
        /// 临时
        /// </summary>
        public class ProdAbnormal_DetailTemp
        {
            /// <summary>
            /// 排序
            /// </summary>
            public int seq { get; set; }
            /// <summary>
            /// 主键ID
            /// </summary>
            public int id { get; set; }
            /// <summary>
            /// 工单号
            /// </summary>
            public string prod_number1 { get; set; }
            /// <summary>
            /// 站点名称
            /// </summary>
            public string site_name { get; set; }
            /// <summary>
            /// 异常名称
            /// </summary>
            public string status_name { get; set; }
            /// <summary>
            /// 异常数量
            /// </summary>
            public decimal abnormal_qty { get; set; }
            /// <summary>
            /// 异常原因名称
            /// </summary>
            public string reason_name { get; set; }
            /// <summary>
            /// 原因备注
            /// </summary>
            public string remark { get; set; }
            /// <summary>
            /// 异常开始时间
            /// </summary>
            public DateTime? begin_time { get; set; }
            /// <summary>
            /// 异常结束时间
            /// </summary>
            public DateTime? end_time { get; set; }
            /// <summary>
            /// 创建人
            /// </summary>
            public string real_name { get; set; }
        }
        /// <summary>
        /// 扇形图图片
        /// </summary>
        /// <returns></returns>
        public List<object> SelectViewData(string ProdNumber, string PlayDate, string EndDate)
        {
            try
            {
                List<int> deptIds = bs.SelectUserDept();
                string deptStr = "";
                for (int i = 0; i < deptIds.Count; i++)
                {
                    deptStr += "'" + deptIds[i] + "'" + ",";
                }
                deptStr = deptStr.Substring(0, deptStr.Length - 1);
                return Db.SqlQueryable<object>($@"WITH TopReasons AS (
                SELECT TOP 6
                    reason_name,
                    COUNT(*) AS count
                FROM
                    abnormal_detail T1 left join target_prod_process 2 on T1.prod_number = T2.prod_number 
                WHERE
                    convert(char(10),begin_time,120) > '{PlayDate}' AND convert(char(10),begin_time,120) < '{EndDate}' and T1.Prod_Number like '%{ProdNumber}%' and T2.dept_id in ({deptStr})
                GROUP BY
                    reason_name
                ORDER BY
                    COUNT(*) DESC
            ),
            OtherReasons AS (
                SELECT
                    '其他' AS reason_name,
                    COUNT(*) AS count
                FROM
                    abnormal_detail T1 left join target_prod_process 2 on T1.prod_number = T2.prod_number 
                WHERE
                    reason_name NOT IN (SELECT reason_name FROM TopReasons) and convert(char(10),begin_time,120) > '{PlayDate}' AND convert(char(10),begin_time,120) < '{EndDate}' and Prod_Number like '%{ProdNumber}%'
                    and T2.dept_id in ({deptStr})
            ),
            AllReasons AS (
                SELECT * FROM TopReasons
                UNION ALL
                SELECT * FROM OtherReasons
            )
            SELECT
                reason_name,
                count,
                CAST((count * 100.0) / SUM(count) OVER() AS DECIMAL(5,2)) AS percentage
            FROM
                AllReasons;").ToList();
            }
            catch (Exception ex)
            {
                List<object> nmsl = new List<object>();
                return nmsl;
                throw;
            }
        }
        /// <summary>
        /// 获取异常平均时间
        /// </summary>
        /// <returns></returns>
        public string AvgTime(string PlayDate, string EndDate)
        {
            var list = Db.SqlQueryable<object>($@"SELECT 
                CONCAT(
                    CAST(avg_total_minutes / 1440 as VARCHAR), ' 天 ',
                    CAST((avg_total_minutes % 1440) / 60 as VARCHAR), ' 小时 ',
                    CAST(avg_total_minutes % 60 as VARCHAR), ' 分'
                ) AS formatted_average_total_time
            FROM
            (
                SELECT 
                    AVG(DATEDIFF(minute, begin_time, end_time)) AS avg_total_minutes
                FROM
                    abnormal_detail T1
                    LEFT JOIN base_user T2 ON T1.create_user = T2.user_name
                where
					begin_time >= '{PlayDate}' AND  begin_time <= '{EndDate}'
            ) AS subquery").ToList();
            dynamic dylist = list;
            return dylist[0].formatted_average_total_time;
        }

        /// <summary>
        /// 不良率图片
        /// </summary>
        /// <returns></returns>
        public List<object> SelectNotGoodNumber(string ProdNumber, string PlayDate, string EndDate)
        {
            try
            {
                return Db.SqlQueryable<object>($@"SELECT
                    CASE
                        WHEN end_time IS NULL THEN CONCAT(T1.prod_number, ' (未处理)')
                        ELSE T1.prod_number
                    END AS prod_number_with_status,
                    CAST(SUM(abnormal_qty) / prod_qty * 100 AS INT) AS abnormal_rate,
                    end_time,
	                site_name
                FROM
                    abnormal_detail T1
                LEFT JOIN target_prod_process T2 ON T1.prod_number = T2.prod_number 
                where
                    begin_time  >= '{PlayDate}' AND   begin_time  <= '{EndDate}'
                GROUP BY
                    CASE
                        WHEN end_time IS NULL THEN CONCAT(T1.prod_number, ' (未处理)')
                        ELSE T1.prod_number
                    END,
                    T2.prod_qty,
                    end_time,
	                site_name").ToList();
            }
            catch (Exception ex)
            {
                List<object> nmsl = new List<object>();
                return nmsl;
                throw;
            }
        }
    }
}
